#!/usr/bin/env python
# -*- coding:utf-8 -*-

import sys
from optparse import OptionParser
import EkikaraDb
import getTimetableLog

COUNT_STATION_BY_TRAIN_SQL = u"""
select train_number, count(station_name) cnt
from train_time
where timetable_id = 0
group by train_number
order by count(station_name) desc, train_number asc
"""

SELECT_TRAIN_HEADER_SQL = u"""
select train_number, train_name, train_type, note, gou, url
from train_header
where timetable_id = ? and train_number = ?
"""

TRAIN_STATION_LIST_SQL = u"""
SELECT station_order, station_name, train_arrival, train_departure
FROM train_time
where timetable_id = 0 and train_number = ?
order by station_order
"""


def wrap(s):
    if s != None:
        return u'u"%s"' % (s.strip())
    else:
        return s


def main():
    logger = getTimetableLog.logging.getLogger("gettimetable")
    optParser = OptionParser(usage="%prog [options]", version="%prog 0.1")
    optParser.add_option("-s", "--status",
                         action="store_true",
                         dest="status",
                         help="status messages to stderr")
    optParser.add_option("", "--db-file",
                         dest="dbFile",
                         help="use sqlite db file. less then use:memory:",
                         metavar="FILE")
    optParser.add_option("", "--train-number",
                         dest="trainNumber",
                         help="put specified train detail",
                         metavar="TRAIN_NUMBER")
    optParser.add_option("", "--station-count",
                         action="store_true",
                         dest="stationCount",
                         help="station count by train")
    (OPTIONS, args) = optParser.parse_args()
    if OPTIONS.dbFile:
        db = EkikaraDb.EkikaraDb(filename=OPTIONS.dbFile, logger=logger)
    else:
        optParser.error("please specify sqlite DB file.")
    if OPTIONS.stationCount:
        sys.stdout.write((u"station count desc by train train number(count):\n").encode(CONSOLE_ENCODE))
        for row in db.conn.execute(COUNT_STATION_BY_TRAIN_SQL):
            sys.stdout.write((u"[%s(%s駅)]" %
                              (row['train_number'], row['cnt'])).encode(CONSOLE_ENCODE))
        sys.stdout.write(u"\n".encode(CONSOLE_ENCODE))
    elif OPTIONS.trainNumber:
        headers = db.conn.execute(SELECT_TRAIN_HEADER_SQL, (0, OPTIONS.trainNumber,))
        header = headers.fetchone()
        if not header or header[0] == None:
            logger.error("can not fetch train_header for timetable_id=[%d][%s]"
                         % (0, OPTIONS.trainNumber))
            sys.exit("can not fetch train_header for timetable_id=[%d][%s]"
                     % (0, OPTIONS.trainNumber))
        train_name = wrap(header['train_name'])
        train_number = wrap(header['train_number'])
        train_type = wrap(header['train_type'])
        note = wrap(header['note'])
        gou = wrap(header['gou'])
        url = wrap(header['url'])
        sys.stdout.write((u"""
    train = {
            'name':   %s,
            'number': %s,
            'type':   %s,
            'note':   %s,
            'gou':    %s,
            'url':    %s,
    """ % (train_name, train_number, train_type, note, gou, url)).encode(CONSOLE_ENCODE))
        sys.stdout.write(u"'stations': [\n".encode(CONSOLE_ENCODE))
        for station in db.conn.execute(TRAIN_STATION_LIST_SQL, (OPTIONS.trainNumber, )):
            station_name = wrap(station['station_name'])
            train_arrival = wrap(station['train_arrival'])
            train_departure = wrap(station['train_departure'])
            sys.stdout.write((u"{'name': %s, 'arrival': %s , 'departure': %s,},\n"
                              % (station_name,
                                 train_arrival,
                                 train_departure)).encode(CONSOLE_ENCODE))
        sys.stdout.write(u"]}\n".encode(CONSOLE_ENCODE))

# ---------- main
if __name__ == '__main__':
    main()
